package com.yixun.qfbao.utils;

import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.gson.JsonArray;
import com.yixun.qfbao.apidto.SmsReportPageReqDto;
import com.yixun.qfbao.hander.BatchAddPremisesRecommenderHander;
import com.yixun.qfbao.model.DevPremisesExport;
import com.yixun.qfbao.model.SmsHistory;
import com.yixun.qfbao.util.DateStyle;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.yixun.qfbao.model.AgentRankReport;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class Export {
	private static Logger logger= LoggerFactory.getLogger(Export.class);

	/**
	 * 导出到excel
	 */
	public static void exportExcel(List<List<Map<String, String>>> list, List<String[]> headerList, String[] name,
			HttpServletResponse response) throws Exception{
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String mimetype = "application/x-msdownload";
		String downFileName = df.format(new Date()) + ".xls";
		String inlineType = "attachment";
		response.setHeader("Content-Disposition", inlineType
				+ ";filename=\"" + downFileName + "\"");
		OutputStream out = response.getOutputStream();
		response.setContentType(mimetype);
		try {
			@SuppressWarnings("resource")
			HSSFWorkbook wb = new HSSFWorkbook();
			for(int n = 0; n < name.length; n++){
				HSSFSheet sheet = wb.createSheet();
				wb.setSheetName(n, name[n]);
				HSSFCellStyle headStyle = wb.createCellStyle();
				HSSFFont f  = wb.createFont();
				// 字号
				f.setFontHeightInPoints((short) 12);
				// 加粗
				f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				headStyle.setFont(f);
				// 创建一个居中格式
				headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				headStyle.setBorderBottom((short) 1);
				headStyle.setBorderRight((short) 1);
				headStyle.setFillBackgroundColor((short) 20);
				HSSFRow row = sheet.createRow(0);
				//创建单元格，并设置值表头 设置表头居中
				HSSFCellStyle style = wb.createCellStyle();
				// 创建一个居中格式
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				style.setBorderBottom((short) 1);
				style.setBorderRight((short) 1);

				HSSFCell cell = null;
				String[] header = headerList.get(n);
				for (int i = 0; i < header.length; i++) {
					cell = row.createCell(i);
					cell.setCellStyle(style);
					HSSFRichTextString text = new HSSFRichTextString(header[i]);
					cell.setCellValue(text);
			  	}

			  	List<Map<String, String>> mapList = list.get(n);
			  	for (int j = 0; j < mapList.size(); j++) {
				  	Map<String, String> map = mapList.get(j);
				  	row = sheet.createRow((int) j + 1);
				  	for (int i = 0; i < header.length; i++) {
					  	cell = row.createCell(i,1);
					  	cell.setCellValue(StringUtils.isBlank(map.get(header[i]))?"":map.get(header[i]));
				  	}
			 	}
		 	}
			wb.write(out);
		} catch (Exception e) {
			logger.error("exportExcel error: {}", e);
		}finally {
			out.flush();
			out.close();
		}
	}
	/**
	 * 导出到excel
	 */
	public static void exportChannelEffectExcel(List<List<Map<String, String>>> list, List<String[]> headerList, String[] name,String fileName,
			HttpServletResponse response) throws Exception{

		String mimetype = "application/x-msdownload";
		String inlineType = "attachment";
		response.setHeader("Content-Disposition", inlineType
				+ ";filename=" + new String(fileName.getBytes(),"iso-8859-1") + ".xls");
		OutputStream out = response.getOutputStream();
		response.setContentType(mimetype);
		try {
			@SuppressWarnings("resource")
			HSSFWorkbook wb = new HSSFWorkbook(); 
			for(int n = 0; n < name.length; n++){
				HSSFSheet sheet = wb.createSheet();  
				wb.setSheetName(n, name[n]);  
				HSSFCellStyle headStyle = wb.createCellStyle(); 
				HSSFFont f  = wb.createFont();      
				f.setFontHeightInPoints((short) 12);// 字号   
				f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗   
				headStyle.setFont(f); 
				headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
				headStyle.setBorderBottom((short) 1);
				headStyle.setBorderRight((short) 1);
				headStyle.setFillBackgroundColor((short) 20);
				HSSFRow row = sheet.createRow(0); 
				//创建单元格，并设置值表头 设置表头居中  
				HSSFCellStyle style = wb.createCellStyle();  
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
				style.setBorderBottom((short) 1);
				style.setBorderRight((short) 1);
				
				HSSFCell cell = null;
				String[] header = headerList.get(n);
				for (int i = 0; i < header.length; i++) {  
					cell = row.createCell(i);  
					cell.setCellStyle(style);  
					HSSFRichTextString text = new HSSFRichTextString(header[i]);  
					cell.setCellValue(text);  
				}  
				
				List<Map<String, String>> mapList = list.get(n);
				for (int j = 0; j < mapList.size(); j++) {
					Map<String, String> map = mapList.get(j);
					row = sheet.createRow((int) j + 1);  
					for (int i = 0; i < header.length; i++) {
						cell = row.createCell(i,1);
						cell.setCellValue(StringUtils.isBlank(map.get(header[i])+"_"+j)?"":map.get(header[i]+"_"+j));
					}
				}
			}

			wb.write(out);

		} catch (Exception e) {
			logger.error("exportChannelEffectExcel error: ", e, "," + "导出到excel失败!");
		}finally {
			out.flush();
			out.close();
		}
	}
	/**
	 * 导出到excel
	 */
	public static void exportReportExcel(List<List<Map<String, String>>> list, List<String[]> headerList, String[] name,
			HttpServletResponse response) throws Exception{

		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String mimetype = "application/x-msdownload";
		String downFileName = df.format(new Date()) + ".xlsx";
		String inlineType = "attachment";
		response.setHeader("Content-Disposition", inlineType
				+ ";filename=\"" + downFileName + "\"");
		OutputStream out = response.getOutputStream();
		response.setContentType(mimetype);

		try {
			@SuppressWarnings("resource")
			XSSFWorkbook wb = new XSSFWorkbook();
			for(int n = 0; n < name.length; n++){
				XSSFSheet sheet = wb.createSheet();  
				wb.setSheetName(n, name[n]);  
				XSSFCellStyle headStyle = wb.createCellStyle(); 
				XSSFFont f  = wb.createFont();      
				f.setFontHeightInPoints((short) 12);// 字号   
				f.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 加粗   
				headStyle.setFont(f); 
				headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
				headStyle.setBorderBottom((short) 1);
				headStyle.setBorderRight((short) 1);
				headStyle.setFillBackgroundColor((short) 20);
				XSSFRow row = sheet.createRow(0); 
				//创建单元格，并设置值表头 设置表头居中  
				XSSFCellStyle style = wb.createCellStyle();  
				style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
				style.setBorderBottom((short) 1);
				style.setBorderRight((short) 1);
				
				XSSFCell cell = null;
				String[] header = headerList.get(n);
				for (int i = 0; i < header.length; i++) {  
					cell = row.createCell(i);  
					cell.setCellStyle(style);  
					XSSFRichTextString text = new XSSFRichTextString(header[i]);  
					cell.setCellValue(text);  
				}  
				
				List<Map<String, String>> mapList = list.get(n);
				for (int j = 0; j < mapList.size(); j++) {
					Map<String, String> map = mapList.get(j);
					row = sheet.createRow((int) j + 1);  
					for (int i = 0; i < header.length; i++) {
						cell = row.createCell(i,1);
						cell.setCellValue(StringUtils.isBlank(map.get(header[i]))?"":map.get(header[i]));
					}
				}
			}
			

			wb.write(out);
		} catch (Exception e) {
			logger.error("exportReportExcel error: ", e, "," + "导出到excel失败!");
		}finally {
			out.flush();
			out.close();
		}
	}
	/**
	 * 导出报备到excel
	 */
	public static void exportCustomerReportExcel(String fileName,List<List<Map<String, String>>> list, List<String[]> headerList, String[] name,
			HttpServletResponse response) throws Exception{

		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String mimetype = "application/x-msdownload";
		String downFileName =fileName+"-"+ df.format(new Date()) + ".xlsx";
		String inlineType = "attachment";
		response.setCharacterEncoding("UTF-8");
		response.setHeader("Content-Disposition", inlineType
				+ ";filename=\"" + URLEncoder.encode(downFileName, "utf-8") + "\"");
		OutputStream out = response.getOutputStream();
		response.setContentType(mimetype);

		try {
			@SuppressWarnings("resource")
			XSSFWorkbook wb = new XSSFWorkbook();
			for(int n = 0; n < name.length; n++){
				XSSFSheet sheet = wb.createSheet();  
				wb.setSheetName(n, name[n]);  
				XSSFCellStyle headStyle = wb.createCellStyle(); 
				XSSFFont f  = wb.createFont();      
				f.setFontHeightInPoints((short) 12);// 字号   
				f.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 加粗   
				headStyle.setFont(f); 
				headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
				headStyle.setBorderBottom((short) 1);
				headStyle.setBorderRight((short) 1);
				headStyle.setFillBackgroundColor((short) 20);
				XSSFRow row = sheet.createRow(0); 
				//创建单元格，并设置值表头 设置表头居中  
				XSSFCellStyle style = wb.createCellStyle();  
				style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
				style.setBorderBottom((short) 1);
				style.setBorderRight((short) 1);
				
				XSSFCell cell = null;
				String[] header = headerList.get(n);
				for (int i = 0; i < header.length; i++) {  
					cell = row.createCell(i);  
					cell.setCellStyle(style);  
					XSSFRichTextString text = new XSSFRichTextString(header[i]);  
					cell.setCellValue(text);  
				}  
				
				List<Map<String, String>> mapList = list.get(n);
				for (int j = 0; j < mapList.size(); j++) {
					Map<String, String> map = mapList.get(j);
					row = sheet.createRow((int) j + 1);  
					for (int i = 0; i < header.length; i++) {
						cell = row.createCell(i,1);
						cell.setCellValue(StringUtils.isBlank(map.get(header[i]))?"":map.get(header[i]));
					}
				}
			}
			wb.write(out);
		} catch (Exception e) {
			logger.error("exportCustomerReportExcel error: {}", e);
		}finally {
			out.flush();
			out.close();
		}
	}

	/**
	 * 导出到excel
	 * 佣金设置详情
	 */
	public static void exportCommisseionSetDetailExcel(String fileName,List<List<Map<String, String>>> list, List<String[]> headerList, String[] name,
			 HttpServletResponse response) throws Exception{
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String mimetype = "application/x-msdownload";
		String downFileName =fileName+"-"+ df.format(new Date()) + ".xlsx";
		String inlineType = "attachment";
		response.setCharacterEncoding("UTF-8");
		response.setHeader("Content-Disposition", inlineType
				+ ";filename=\"" +URLEncoder.encode(downFileName, "utf-8")+ "\"");
		OutputStream out = response.getOutputStream();
		response.setContentType(mimetype);

		try {
			@SuppressWarnings("resource")
			XSSFWorkbook wb = new XSSFWorkbook();
			for(int n = 0; n < name.length; n++){
				XSSFSheet sheet = wb.createSheet();
				wb.setSheetName(n, name[n]);
				XSSFCellStyle headStyle = wb.createCellStyle();
				XSSFFont f  = wb.createFont();
				f.setFontHeightInPoints((short) 12);// 字号
				f.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 加粗
				headStyle.setFont(f);
				headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				headStyle.setBorderBottom((short) 1);
				headStyle.setBorderRight((short) 1);
				headStyle.setFillBackgroundColor((short) 20);

				sheet.setColumnWidth(0,20*100);
				sheet.setColumnWidth(1,70*100);
				sheet.setColumnWidth(2,60*100);
				sheet.setColumnWidth(3,70*100);
				sheet.setColumnWidth(4,110*100);
				sheet.setColumnWidth(5,60*100);
				sheet.setColumnWidth(6,60*100);
				sheet.setColumnWidth(7,40*100);

				XSSFRow row = sheet.createRow(0);
				//创建单元格，并设置值表头 设置表头居中
				XSSFCellStyle style = wb.createCellStyle();
				style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				style.setBorderBottom((short) 1);
				style.setBorderRight((short) 1);

				XSSFCell cell = null;
				String[] header = headerList.get(n);
				for (int i = 0; i < header.length; i++) {
					cell = row.createCell(i);
					cell.setCellStyle(style);
					XSSFRichTextString text = new XSSFRichTextString(header[i]);
					cell.setCellValue(text);
				}

				List<Map<String, String>> mapList = list.get(n);
				for (int j = 0; j < mapList.size(); j++) {
					Map<String, String> map = mapList.get(j);
					row = sheet.createRow((int) j + 1);
					for (int i = 0; i < header.length; i++) {
						cell = row.createCell(i,1);
						cell.setCellValue(StringUtils.isBlank(map.get(header[i]))?"":map.get(header[i]));
					}
				}
			}
			wb.write(out);
		} catch (Exception e) {
			logger.error("exportCommisseionSetDetailExcel error: {}", e);
		}finally {
			out.flush();
			out.close();
		}
	}

	/**
	 * 导出到excel
	 * 经纪人排行表
	 */
	public static void exportAgentExcel(List<AgentRankReport> list, HttpServletResponse response) throws Exception{
		String mimetype = "application/x-msdownload";
		String inlineType = "attachment";
		response.setHeader("Content-Disposition", inlineType  + ";filename=" + new String("经纪人排行表".getBytes(),"iso-8859-1") + ".xls");
		OutputStream out = response.getOutputStream();
		response.setContentType(mimetype);

		try {
			@SuppressWarnings("resource")
			HSSFWorkbook wb = new HSSFWorkbook(); 
			if(null != list && list.size() > 0){
				HSSFSheet sheet = wb.createSheet("经纪人排行表");
				//创建单元格，并设置值表头 设置表头居中  
				HSSFCellStyle style = wb.createCellStyle();
				style.setAlignment(CellStyle.ALIGN_CENTER);
				
				String[] header = new String[]{"TOP10排行榜","经纪人","客户数","报备客户","报备成功客户","到访客户","认筹客户","认购客户","签约客户"};
				HSSFRow row = sheet.createRow((int) 0);
				
				Font font = wb.createFont();
				font.setBoldweight(HSSFFont.SS_NONE);
				font.setFontName("仿宋_GB2312");
				font.setFontHeightInPoints((short) 12);
				style.setFont(font);
				for (int i = 0;i < header.length;i++) {
					sheet.setColumnWidth(i, 3766);
					if(i==0){
						CellRangeAddress cra = new CellRangeAddress(0, 1, 0, 0);
						sheet.addMergedRegion(cra);
					}
					if(i==1){
						CellRangeAddress cra = new CellRangeAddress(0, 1, 1, 1);
						sheet.addMergedRegion(cra);
					}
					if(i==2){
						CellRangeAddress cra = new CellRangeAddress(0, 0, 2, 7);
						sheet.addMergedRegion(cra);
					}
					if(i==3){
						row = sheet.createRow((int) 1);
					}
					HSSFCell cell = null;
					int cellNum = i;
					if(i>=3){
						cell = row.createCell(cellNum-1);
					}else{
						cell = row.createCell(i);
					}
		            cell.setCellStyle(style);
		            HSSFRichTextString text = new HSSFRichTextString(header[i]);
		            cell.setCellValue(text);
		        }
				//数据体基本样式
				HSSFCell cell = null;
				for(int ii=0;ii<list.size();ii++){
					AgentRankReport arr = list.get(ii);
					row = sheet.createRow(ii+2);
					for(int jj=0;jj<8;jj++){
						if(jj==0){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
				            cell.setCellValue("NO."+(ii+1));
						}else if(jj==1){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
				            HSSFRichTextString text = new HSSFRichTextString(arr.getAccountName());
				            cell.setCellValue(text);
						}else if(jj==2){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
				            HSSFRichTextString text = new HSSFRichTextString(String.valueOf(arr.getAgentRankOne()));
				            cell.setCellValue(text);
						}else if(jj==3){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
				            HSSFRichTextString text = new HSSFRichTextString(String.valueOf(arr.getAgentRankTwo()));
				            cell.setCellValue(text);
						}else if(jj==4){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
				            HSSFRichTextString text = new HSSFRichTextString(String.valueOf(arr.getAgentRankThree()));
				            cell.setCellValue(text);
						}else if(jj==5){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
				            HSSFRichTextString text = new HSSFRichTextString(String.valueOf(arr.getAgentRankFour()));
				            cell.setCellValue(text);
						}else if(jj==6){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
				            HSSFRichTextString text = new HSSFRichTextString(String.valueOf(arr.getAgentRankFive()));
				            cell.setCellValue(text);
						}else if(jj==7){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
				            HSSFRichTextString text = new HSSFRichTextString(String.valueOf(arr.getAgentRankSix()));
				            cell.setCellValue(text);
						}
					}
				}
				wb.write(out);
			}
		} catch (Exception e) {
			logger.error("exportAgentExcel error: {}", e);
		}finally {
			out.flush();
			out.close();
		}
	}
	
	/**
	 * 导出到excel
	 */
	public static void exportPremisesFlowExcel(Map<String, String> firstRowMap, List<List<Map<String, String>>> list, List<String[]> headerList, String[] name,String fileName,
			HttpServletResponse response) throws Exception{
		String mimetype = "application/x-msdownload";
		String inlineType = "attachment";
		response.setHeader("Content-Disposition", inlineType
				+ ";filename=" + new String(fileName.getBytes(),"iso-8859-1") + ".xls");
		OutputStream out = response.getOutputStream();
		response.setContentType(mimetype);

		try {
			@SuppressWarnings("resource")
			HSSFWorkbook wb = new HSSFWorkbook(); 
			for(int n = 0; n < name.length; n++){
				HSSFSheet sheet = wb.createSheet();  
				wb.setSheetName(n, name[n]);  
				HSSFCellStyle headStyle = wb.createCellStyle(); 
				HSSFFont f  = wb.createFont();      
				f.setFontHeightInPoints((short) 12);// 字号   
				f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗   
				headStyle.setFont(f); 
				headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
				headStyle.setBorderBottom((short) 1);
				headStyle.setBorderRight((short) 1);
				headStyle.setFillBackgroundColor((short) 20);
				
				if(n == 0) {
					HSSFRow row = sheet.createRow(0);
					//创建单元格，并设置值表头 设置表头居中  
					HSSFCellStyle style = wb.createCellStyle();  
					style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
					style.setBorderBottom((short) 1);
					style.setBorderRight((short) 1);
					
					HSSFCell cell = null;
					cell = row.createCell(0);
					cell.setCellStyle(style);
					HSSFRichTextString text = new HSSFRichTextString("统计日期");
					cell.setCellValue(text);
					
					cell = row.createCell(1);
					cell.setCellStyle(style);
					text = new HSSFRichTextString(firstRowMap.get("beginDate"));
					cell.setCellValue(text);
					
					cell = row.createCell(2);
					cell.setCellStyle(style);
					text = new HSSFRichTextString("至");
					cell.setCellValue(text);
					
					cell = row.createCell(3);
					cell.setCellStyle(style);
					text = new HSSFRichTextString(firstRowMap.get("endDate"));
					cell.setCellValue(text);
				}
				
				HSSFRow row = sheet.createRow(1);
				//创建单元格，并设置值表头 设置表头居中
				HSSFCellStyle style = wb.createCellStyle();  
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
				style.setBorderBottom((short) 1);
				style.setBorderRight((short) 1);
				
				HSSFCell cell = null;
				String[] header = headerList.get(n);
				for (int i = 0; i < header.length; i++) {
					cell = row.createCell(i);
					cell.setCellStyle(style);
					HSSFRichTextString text = new HSSFRichTextString(header[i]);
					cell.setCellValue(text);
				}  
				
				List<Map<String, String>> mapList = list.get(n);
				for (int j = 0; j < mapList.size(); j++) {
					Map<String, String> map = mapList.get(j);
					row = sheet.createRow((int) j + 2);
					for (int i = 0; i < header.length; i++) {
						cell = row.createCell(i,1);
						cell.setCellValue(StringUtils.isBlank(map.get(header[i])+"_"+j)?"":map.get(header[i]+"_"+j));
					}
				}
			}
			
			wb.write(out);
		} catch (Exception e) {
			logger.error("exportPremisesFlowExcel error: {}", e);
		}finally {
			out.flush();
			out.close();
		}
	}

	//短信导出
	public static void exportSmsHistory(List<SmsHistory> smsHisList, SmsReportPageReqDto entity, String companyName,HttpServletResponse response) throws Exception{

		SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
		String downFileName = companyName+"_"+df.format(new Date()) + ".xls";
		downFileName = URLEncoder.encode(downFileName, "UTF-8");
		response.reset();
		response.setCharacterEncoding("utf-8");
		response.setHeader("Content-Disposition","attachment;filename="+downFileName);
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		OutputStream out = response.getOutputStream();

		try {
			@SuppressWarnings("resource")
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet();
			sheet.createFreezePane( 0, 1, 0, 1 );
			//合并第一行
			CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 3);
			sheet.addMergedRegion(cra);
			//合并第二行
			cra = new CellRangeAddress(1, 1, 1, 3);
			sheet.addMergedRegion(cra);
			//合并第三行
			cra = new CellRangeAddress(2, 2, 1, 3);
			sheet.addMergedRegion(cra);
			wb.setSheetName(0, "短信发送记录");

			HSSFCellStyle headStyle = wb.createCellStyle();
			HSSFFont f  = wb.createFont();
			f.setFontHeightInPoints((short) 15);// 字号
			f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
			headStyle.setFont(f);
			headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
			headStyle.setBorderBottom((short) 1);
			headStyle.setBorderRight((short) 1);
			headStyle.setFillBackgroundColor((short) 20);

			HSSFRow row = sheet.createRow(0);
			//创建单元格，并设置值表头 设置表头居中
			HSSFCellStyle style = wb.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
			style.setBorderBottom((short) 1);
			style.setBorderRight((short) 1);

			HSSFCell cell = row.createCell(0);
			cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString("渠道管理短信使用汇总表");
			cell.setCellValue(text);

			//第二行商户名
			row = sheet.createRow(1);
			style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			cell = row.createCell(0);
			cell.setCellStyle(style);
			text = new HSSFRichTextString("商户名：");
			cell.setCellValue(text);
			style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			cell = row.createCell(1);
			cell.setCellStyle(style);
			text = new HSSFRichTextString(companyName);
			cell.setCellValue(text);

			//第三行账期
			row = sheet.createRow(2);
			//创建单元格，并设置值表头 设置表头居中
			style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			cell = row.createCell(0);
			cell.setCellStyle(style);
			text = new HSSFRichTextString("账期：");
			cell.setCellValue(text);
			style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			cell = row.createCell(1);
			cell.setCellStyle(style);
			String timeStr = "";
			if(null != entity.getBeginDate()) {
				timeStr = DateUtils.DateToString(entity.getBeginDate(), DateStyle.YYYY_MM_DD.getValue());
			}else {
				timeStr = "~";
			}
			timeStr += " 至 ";
			if(null != entity.getEndDate()) {
				timeStr += DateUtils.DateToString(entity.getEndDate(), DateStyle.YYYY_MM_DD.getValue());
			}else {
				timeStr += "~";
			}
			text = new HSSFRichTextString(timeStr);
			cell.setCellValue(text);

			//第四行数量统计
			row = sheet.createRow(3);
			//创建单元格，并设置值表头 设置表头居中
			style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			cell = row.createCell(0);
			cell.setCellStyle(style);
			text = new HSSFRichTextString("短信数量总计：");
			cell.setCellValue(text);
			style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			cell = row.createCell(1);
			cell.setCellStyle(style);
			text = new HSSFRichTextString(String.valueOf(smsHisList.size()));
			cell.setCellValue(text);

			row = sheet.createRow(4);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cell = row.createCell(0);
			cell.setCellStyle(style);
			text = new HSSFRichTextString("序号");
			cell.setCellValue(text);
			cell = row.createCell(1);
			cell.setCellStyle(style);
			text = new HSSFRichTextString("类型");
			cell.setCellValue(text);
			cell = row.createCell(2);
			cell.setCellStyle(style);
			text = new HSSFRichTextString("发送目标号码");
			cell.setCellValue(text);
			cell = row.createCell(3);
			cell.setCellStyle(style);
			text = new HSSFRichTextString("发送时间");
			cell.setCellValue(text);

			if(null != smsHisList && smsHisList.size() > 0) {
				for(int ii=0; ii<smsHisList.size(); ii++) {
					SmsHistory smsHis = smsHisList.get(ii);
					row = sheet.createRow(ii + 5);
					cell = row.createCell(0);
					cell.setCellStyle(style);
					text = new HSSFRichTextString(String.valueOf(ii+1));
					cell.setCellValue(text);

					cell = row.createCell(1);
					cell.setCellStyle(style);
					text = new HSSFRichTextString(smsHis.getRemark());
					cell.setCellValue(text);

					cell = row.createCell(2);
					cell.setCellStyle(style);
					text = new HSSFRichTextString(smsHis.getReceiver());
					cell.setCellValue(text);

					cell = row.createCell(3);
					cell.setCellStyle(style);
					text = new HSSFRichTextString(DateUtils.DateToString(smsHis.getCreateTime(), DateStyle.YYYY_MM_DD.getValue()));
					cell.setCellValue(text);
				}
			}
			wb.write(out);
		} catch (Exception e) {
			logger.error("exportSmsHistory error: ", e, "," + "短信导出异常!");
		}finally {
			out.flush();
			out.close();
		}

	}

	/**
	 * 批量报备错误信息导出
	 * @param errorArray
	 * @param header
	 * @param response
	 */
	public static void exportBatchImpError(JSONArray errorArray, String[] header, HttpServletResponse response) throws Exception {
		String mimetype = "application/x-msdownload";
		String inlineType = "attachment";
		response.setHeader("Content-Disposition", inlineType  + ";filename=" + new String("导入错误数据".getBytes(),"iso-8859-1") + ".xls");
		OutputStream out = response.getOutputStream();
		response.setContentType(mimetype);
		try {
			@SuppressWarnings("resource")
			HSSFWorkbook wb = new HSSFWorkbook();
			if(null != errorArray && errorArray.size() > 0){
				HSSFSheet sheet = wb.createSheet("批量报备错误信息");
				//创建单元格，并设置值表头 设置表头居中
				HSSFCellStyle style = wb.createCellStyle();
				style.setAlignment(CellStyle.ALIGN_CENTER);

				HSSFRow row = sheet.createRow((int) 0);

				Font font = wb.createFont();
				font.setBoldweight(HSSFFont.SS_NONE);
				font.setFontName("仿宋_GB2312");
				font.setFontHeightInPoints((short) 12);
				style.setFont(font);
				for (int i = 0; i < header.length; i++) {
					HSSFCell cell = null;
					cell = row.createCell(i);
					cell.setCellStyle(style);
					HSSFRichTextString text = new HSSFRichTextString(header[i]);
					cell.setCellValue(text);
				}
				//数据体基本样式
				HSSFCell cell = null;
				String[] columnName = {"agentName","agentPhone","name","mobile","gender","error"};
				for(int ii=0;ii<errorArray.size();ii++){
					JSONObject object = errorArray.getJSONObject(ii);
					row = sheet.createRow(ii+1);
					for (int jj = 0; jj < columnName.length; jj++) {
						cell = row.createCell(jj);
						cell.setCellStyle(style);
						cell.setCellValue(String.valueOf(object.get(columnName[jj])));
					}
				}
				wb.write(out);
			}
		} catch (Exception e) {
			logger.error("exportBatchImpError error: ", e, "," + "批量报备错误信息导出失败!");
		}finally {
			out.flush();
			out.close();
		}
	}

	public static void exportDevPremisesInfo(List<List<Map<String, String>>> list, List<String[]> headerList, String[] name, String fileName, HttpServletResponse response) throws Exception {
		String mimetype = "application/x-msdownload";
		String inlineType = "attachment";
		response.setHeader("Content-Disposition", inlineType  + ";filename=" + new String(fileName.getBytes(),"iso-8859-1") + ".xls");
		OutputStream out = response.getOutputStream();
		response.setContentType(mimetype);
		try {
			@SuppressWarnings("resource")
			HSSFWorkbook wb = new HSSFWorkbook();
			for(int n = 0; n < name.length; n++){
				HSSFSheet sheet = wb.createSheet();
				wb.setSheetName(n, name[n]);
				HSSFCellStyle headStyle = wb.createCellStyle();
				HSSFFont f  = wb.createFont();
				f.setFontHeightInPoints((short) 12);// 字号
				f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
				headStyle.setFont(f);
				headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				headStyle.setBorderBottom((short) 1);
				headStyle.setBorderRight((short) 1);
				headStyle.setFillBackgroundColor((short) 20);
				HSSFRow row = sheet.createRow(0);
				//创建单元格，并设置值表头 设置表头居中
				HSSFCellStyle style = wb.createCellStyle();
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				style.setBorderBottom((short) 1);
				style.setBorderRight((short) 1);

				HSSFCell cell = null;
				String[] header = headerList.get(n);
				for (int i = 0; i < header.length; i++) {
					cell = row.createCell(i);
					cell.setCellStyle(style);
					HSSFRichTextString text = new HSSFRichTextString(header[i]);
					cell.setCellValue(text);
				}

				List<Map<String, String>> mapList = list.get(n);
				for (int j = 0; j < mapList.size(); j++) {
					Map<String, String> map = mapList.get(j);
					row = sheet.createRow((int) j + 1);
					for (int i = 0; i < header.length; i++) {
						cell = row.createCell(i, 1);
						cell.setCellValue(StringUtils.isBlank(map.get(header[i]) + "_" + j) ? "" : map.get(header[i] + "_" + j));
					}
				}
			}

			wb.write(out);

		} catch (Exception e) {
			logger.error("exportChannelEffectExcel error: ", e, "," + "导出到excel失败!");
		}finally {
			out.flush();
			out.close();
		}
	}
}
